﻿using MySqlConnector;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory.Database;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using System.Text.RegularExpressions;
using System.Reflection;
using CBS.Model.ManageModel;
using NPOI.SS.Formula.Functions;
using System.Reflection.Metadata;
using System.Runtime.CompilerServices;
using MathNet.Numerics.Statistics.Mcmc;

namespace CBS.DB
{
    public static class DBHelper
    {
        private static string ConnectionStr = "server=39.99.40.36;database=cbs;uid=cbsadmin;pwd=cbsadmin123456_+";
        

        private static DataTable SqlQuery(string connectionStr, string sql, params MySqlParameter[] cmdParms)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionStr))
            {
                connection.Open();
                var command = CreateCommand(sql, connection, cmdParms);

                using (var adapter = new MySqlDataAdapter(command))
                {
                    var dataTable = new DataTable();
                    adapter.Fill(dataTable);
                    return dataTable;
                }
            }
        }

        private static DataSet SqlQuery(string connectionStr, string sql, List<string> tableName)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionStr))
            {
                connection.Open();
                var command = CreateCommand(sql, connection);

                using (var adapter = new MySqlDataAdapter(command))
                {
                    var dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    for (int i = 0; i < dataSet.Tables.Count; i++)
                    {
                        dataSet.Tables[i].TableName = tableName[i];
                    }

                    return dataSet;
                }
            }
        }

        private static List<T> ToList<T>(this DataTable dt) where T : class, new()
        {
            var propertyInfos = typeof(T).GetProperties();
            var list = new List<T>();
            foreach (DataRow row in dt.Rows)
            {
                var t = new T();
                foreach (PropertyInfo p in propertyInfos)
                {
                    if (dt.Columns.IndexOf(p.Name) != -1 && row[p.Name] != DBNull.Value)
                        p.SetValue(t, row[p.Name], null);
                }
                list.Add(t);
            }
            return list;
        }

        public static List<T> DateTableToList<T>(DataTable dt) where T : class, new()
        {
            var propertyInfos = typeof(T).GetProperties();
            var list = new List<T>();
            foreach (DataRow row in dt.Rows)
            {
                var t = new T();
                foreach (PropertyInfo p in propertyInfos)
                {
                    if (dt.Columns.IndexOf(p.Name) != -1 && row[p.Name] != DBNull.Value)
                        p.SetValue(t, row[p.Name], null);
                }
                list.Add(t);
            }
            return list;
        }

        private static List<T> SqlQuery<T>(string connectionStr, string sql, params MySqlParameter[] cmdParms) where T : class, new()
        {
            var dt = SqlQuery(connectionStr, sql, cmdParms);
            return dt.ToList<T>();
        }

        private static MySqlCommand CreateCommand(string sql, MySqlConnection connection, params MySqlParameter[] cmdParms)
        {
            var cmd = connection.CreateCommand();
            cmd.CommandText = sql;
            if (cmdParms != null)
                cmd.Parameters.AddRange(cmdParms);
            return cmd;
        }

        private static int SqlCount(string connectionStr, string sql, params MySqlParameter[] cmdParms)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionStr))
            {
                connection.Open();
                var command = CreateCommand(sql, connection, cmdParms);
                var re = command.ExecuteScalar();
                connection.Close();
                int reNumber = 0;
                try
                {
                    Int32.TryParse(re.ToString(), out reNumber);
                }
                catch (Exception ex)
                {
                    //LogAsyncWriter.Default.Error(ex, nameof(OMSContextExtensions));
                    return 0;
                }
                command.Parameters.Clear();
                command.Dispose();
                return reNumber;
            }
        }
        public static List<TEntity> FindListOrderBy<TEntity>(string strSql, Pagination pagination, FromPositionInSql fromEnum, params MySqlParameter[] cmdParms) where TEntity : class, new()
        {
            if (cmdParms == null)
            {
                cmdParms = new MySqlParameter[0];
            }

            //var sqlSplit = strSql.ToUpper().Split(new string[] { "FROM" }, StringSplitOptions.RemoveEmptyEntries).ToList();
            var sqlSplit = Regex.Split(strSql, "FROM", RegexOptions.IgnoreCase).ToList();
            string countStr = "";
            switch (fromEnum)
            {
                case FromPositionInSql.All:
                    countStr = "SELECT COUNT(1) as count FROM (" + strSql + ") t ";
                    break;
                case FromPositionInSql.Last:
                    string sqlfromtempf = sqlSplit[sqlSplit.Count - 1];
                    countStr = "SELECT COUNT(1) as count FROM" + string.Join(" FROM ", sqlSplit[sqlSplit.Count - 1]);
                    break;
                default:
                    sqlSplit.RemoveAt(0);
                    countStr = "SELECT COUNT(1) as count FROM" + string.Join(" FROM ", sqlSplit);
                    break;
            }
            pagination.records = SqlCount(ConnectionStr, countStr, cmdParms);

            if (pagination.records == 0)
            {
                return new List<TEntity>();
            }

            StringBuilder sqlorders = new StringBuilder();
            if (!string.IsNullOrEmpty(pagination.sidx) && !string.IsNullOrEmpty(pagination.sord))
            {
                bool isAsc = pagination.sord.ToLower() == "asc" ? true : false;
                string[] _order = pagination.sidx.Split(',');
                foreach (var item in _order)
                {
                    string _orderPart = item;
                    _orderPart = Regex.Replace(_orderPart, @"\s+", " ");
                    string[] _orderArry = _orderPart.Split(' ');
                    string _orderField = _orderArry[0];
                    bool sort = isAsc;
                    if (_orderArry.Length == 2)
                    {
                        isAsc = _orderArry[1].ToUpper() == "ASC" ? true : false;
                    }
                    sqlorders.AppendFormat(", {0} {1} ", _orderField, isAsc ? "asc" : "desc");
                }
                sqlorders = sqlorders.Length > 0 ? sqlorders.Remove(0, 1) : sqlorders;
            }
            string sqlFormat = @"select *
                                from (select *, ROW_NUMBER() over (order by {1}) AS RowNumber
                                      from ({0}) a) T
                                where RowNumber between " + ((pagination.page - 1) * pagination.rows + 1) + @" and " + (pagination.page * pagination.rows) + @"
                                order by {1}";
            strSql = string.Format(sqlFormat, strSql, sqlorders);
            var reList = SqlQuery<TEntity>(ConnectionStr, strSql, cmdParms);
            return reList;
        }

        public static List<TEntity> FindList<TEntity>(string strSql, params MySqlParameter[] cmdParms) where TEntity : class, new()
        {
            if (cmdParms == null)
            {
                cmdParms = new MySqlParameter[0];
            }

            var reList = SqlQuery<TEntity>(ConnectionStr, strSql, cmdParms);
            return reList;
        }

        public static DataTable FindList(string sql, params MySqlParameter[] cmdParms)
        {
            return SqlQuery(ConnectionStr, sql, cmdParms);
        }

        public static DataSet FindDataSet(string sql, List<string> tableNames)
        {
            return SqlQuery(ConnectionStr, sql, tableNames);
        }


        /// <summary>
        /// 复制DataTable到数据库,需要手动补全自增主键
        /// </summary>
        /// <param name="connectionString">目标连接字符</param>
        /// <param name="TableName">目标表</param>
        /// <param name="dt">源数据</param>
        public static void SqlBulkCopyByDatatable(string TableName, DataTable dt)
        {
            try
            {
                using (MySqlConnection conn = new MySqlConnection(ConnectionStr))
                {
                    var mySqlBulkCopy = new MySqlBulkCopy(conn);
                    mySqlBulkCopy.DestinationTableName = TableName;



                    mySqlBulkCopy.BulkCopyTimeout = 300;
                    mySqlBulkCopy.DestinationTableName = TableName;
                    //for (int i = 0; i < dt.Columns.Count; i++)
                    //{
                    //    sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
                    //}
                    mySqlBulkCopy.ColumnMappings.AddRange(dt.Columns.Cast<DataColumn>().Select(colum => new MySqlBulkCopyColumnMapping { DestinationColumn = colum.ColumnName, SourceOrdinal = colum.Ordinal }).ToList());
                    var result = mySqlBulkCopy.WriteToServer(dt);

                    // check for problems
                    if (result.Warnings.Count != 0) { /* handle potential data loss warnings */ }

                }
            }
            catch (System.Exception ex)
            {

                throw;
            }
        }

        public static DataTable ListToDataTable<T>(IEnumerable<T> varlist, List<string> filedSetList = null)
        {   //定义要返回的DataTable对象
            DataTable dtReturn = new DataTable();
            // 保存列集合的属性信息数组
            PropertyInfo[] oProps = null;
            if (varlist == null) return dtReturn;//安全性检查
                                                 //循环遍历集合，使用反射获取类型的属性信息
            foreach (T rec in varlist)
            {
                //使用反射获取T类型的属性信息，返回一个PropertyInfo类型的集合
                if (oProps == null)
                {
                    oProps = ((Type)rec.GetType()).GetProperties();
                    //循环PropertyInfo数组
                    foreach (PropertyInfo pi in oProps)
                    {
                        if (filedSetList != null && filedSetList.Count > 0)
                        {
                            if (!filedSetList.Contains(pi.Name)) continue;
                        }
                        Type colType = pi.PropertyType;//得到属性的类型
                                                       //如果属性为泛型类型
                        if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition()
                        == typeof(Nullable<>)))
                        {   //获取泛型类型的参数
                            colType = colType.GetGenericArguments()[0];
                        }
                        //将类型的属性名称与属性类型作为DataTable的列数据
                        dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
                    }
                }
                //新建一个用于添加到DataTable中的DataRow对象
                DataRow dr = dtReturn.NewRow();
                //循环遍历属性集合
                foreach (PropertyInfo pi in oProps)
                {   //为DataRow中的指定列赋值
                    dr[pi.Name] = pi.GetValue(rec, null) == null ?
                        DBNull.Value : pi.GetValue(rec, null);
                }
                //将具有结果值的DataRow添加到DataTable集合中
                dtReturn.Rows.Add(dr);
            }
            return dtReturn;//返回DataTable对象
        }

    }

    public enum FromPositionInSql
    {
        First,
        Last,
        All
    }
}
